package school.entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import jdbc.homework01.DbUtil;

public class SubjectDao {

	// 查询所有科目
	public List<Subject> findAll() {

		Connection con = DbUtil.getConnection();
		Statement st = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from s_subject";
		List<Subject> list = new ArrayList<Subject>();
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String subjectname = rs.getString(2);
				int classhour = rs.getInt(3);
				int gradeid = rs.getInt(4);
				Subject s = new Subject(id, subjectname, classhour, gradeid);
				list.add(s);
			}
			return list;
		} catch (SQLException e) {
			// TODO 自动生成的 catch
			e.printStackTrace();
		} finally {
			DbUtil.close(rs, st, ps, con);
		}

		return null;
	}

//增加学科
	public boolean addSubject(Subject s) {
		boolean flag = false;
		Scanner in = new Scanner(System.in);
		Connection con = DbUtil.getConnection();
		// Statement st = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "insert into s_subject values(?,?,?,?)";
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, s.getId());
			ps.setString(2, s.getSubjectname());
			ps.setInt(3, s.getClasshour());
			ps.setInt(4, s.getGradeid());
			int r = ps.executeUpdate();
			if (r != 0) {
				flag = true;
			} else {
				flag = false;
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, ps, con);
		}

		return flag;
	}

// 分页查询
	public List<Subject> limiterSelect(int pageNo, int pagesize) {

		Connection con = DbUtil.getConnection();
		Statement st = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from s_subject limit ?,?";
		List<Subject> list = new ArrayList<Subject>();
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String subjectname = rs.getString(2);
				int classhour = rs.getInt(3);
				int gradeid = rs.getInt(4);
				Subject s = new Subject(id, subjectname, classhour, gradeid);
				list.add(s);
			}
			return list;
		} catch (SQLException e) {
			// TODO 自动生成的 catch
			e.printStackTrace();
		} finally {
			DbUtil.close(rs, st, ps, con);
		}

		return null;

	}

	// 删除 学科
	public static int delete(Subject sb) {
		int r = 0;
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		String sql = "delete from s_subject where id=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, sb.getId());
			r = ps.executeUpdate();
			return r;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.close(null, null, ps, con);
		}

		return r;
	}

	// 修改学科信息
	public static int update(Subject sb) {
		int r = 0;
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		String sql = "update s_subject set subjectname=?, classhour=? where id=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, sb.getSubjectname());
			ps.setInt(2, sb.getClasshour());
			ps.setLong(3, sb.getId());
			r = ps.executeUpdate();
			return r;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.close(null, null, ps, con);
		}

		return r;
	}

}
